package server;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
//import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.mail.MessagingException;
import javax.mail.internet.AddressException;
import javax.swing.JOptionPane;

import entities.User;
import server.mysqlConnection;
import ocsf.server.ConnectionToClient;
/**
 * Query controllers for the Librarian
 * @author Benny
 *
 */
public class LibrarianQueryHandle 
{

	public mysqlConnection sqlCon;
	public MailSystem ms;
	/**
	 * Constructor of the object
	 * @param c established connection to the SQL DataBase
	 */
	public LibrarianQueryHandle(mysqlConnection c)
	{
		this.sqlCon=c;
	}
	
	@SuppressWarnings("static-access")
	/**
	 * Handling message from client and sending an answer
	 * @param msg received message from a specific client, through the running server
	 * @param client a specific client connected to the running server
	 * @throws SQLException
	 * @throws IOException
	 */
	public void handleMessage (Object msg, ConnectionToClient client) throws SQLException, IOException 
	{
		@SuppressWarnings("unchecked")
		ArrayList<Object> messages=(ArrayList<Object>) msg;
		ArrayList<Object> res=new ArrayList<>();;
		String command = (String) messages.get(1);
		res.add(messages.get(0).toString()); // add role
		res.add(messages.get(1).toString()); // add type
		/**
		 * Query controller for borrowing a specific copy to a specific user
		 */
		if(command.equals("BorrowBook"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int copyID=(int)messages.get(2);
			int userID=(int)messages.get(3);
			Calendar cal = Calendar.getInstance();
			Calendar calNext = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			System.out.println(today);
			ResultSet rs = stmt.executeQuery("SELECT * FROM bookcopies WHERE inBorrow='no' AND copyID='" + copyID + "';");
			if(rs.next())
			{
				int bookCode=rs.getInt(5);
				ResultSet rs1 = stmt.executeQuery("SELECT * FROM users WHERE cardStatus='ok' AND userID='"+ userID+ "';");
				System.out.println("user card status is ok");
				if(rs1.next())
				{
					ResultSet rs2 = stmt.executeQuery("SELECT * FROM books WHERE bookCode='"+ bookCode + "';");
					rs2.next();
					String rated=rs2.getString(10);
					System.out.println("test the rated column");
					System.out.println(rated);
					Statement stmtUpdt = sqlCon.conn.createStatement();
					int result = stmtUpdt.executeUpdate("UPDATE bookcopies SET inBorrow='yes' WHERE copyID='" + copyID + "';");
					if(rated.equals("no"))
					{
						calNext.add(Calendar.DATE, 4);
						String isNotRated=sdf.format(calNext.getTime());
						int result2 = stmtUpdt.executeUpdate("INSERT INTO borrows (userID,copyID,borrowStatus,borrowDate,returnDate,actualReturnDate) VALUES('"+userID+"','"+copyID+"','active','"+today+"','"+isNotRated+"','"+isNotRated+"');");
						if(result2>0 && result>0)
						{
							rs2=stmt.executeQuery("SELECT * FROM orders WHERE copyOrderID='"+ copyID +"'AND userOrderID='"+userID+"'AND orderStatus='active';");
							if(rs2.next())
							{
								result2=stmtUpdt.executeUpdate("UPDATE orders SET orderStatus='inactive' WHERE copyOrderID='"+ copyID +"'AND userOrderID='"+userID+"';");
								if(result2>0)
									res.add("OK");
								else
									res.add("Fail");
							}
							else
								res.add("OK");
						}
						else
							res.add("Fail");
					}
					else if(rated.equals("yes"))
					{
						calNext.add(Calendar.DATE, 3);
						String isNotRated=sdf.format(calNext.getTime());
						int result2 = stmtUpdt.executeUpdate("INSERT INTO borrows (userID,copyID,borrowStatus,borrowDate,returnDate,actualReturnDate) VALUES('"+userID+"','"+copyID+"','active','"+today+"','"+isNotRated+"','"+isNotRated+"');");
						if(result2>0 && result>0)
							res.add("OK");
						else
							res.add("Fail");
					}
				}
				else
					res.add("Fail");	
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for a card of a specific userID
		 */
		if(command.equals("ViewUserCard2"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				res.add("Error");
				e.printStackTrace();
			}
			int userID=(int)messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE userID='" + userID + "';");
			if(rs.next())
			{
				User user=new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("phone"), rs.getString("email"),
		                   rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
				res.add("OK");
				res.add((Object)user);
			}
			else
			{
				res.add("Error");
			}
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for returning a specific copy
		 */
		if(command.equals("ReturnBook"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int copyID=(int)messages.get(2);
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			System.out.println(today);
			ResultSet rs = stmt.executeQuery("SELECT * FROM bookcopies WHERE inBorrow='yes' AND copyID='" + copyID + "';");
			if(rs.next())
			{
				Statement stmtUpdt = sqlCon.conn.createStatement();
				int update = stmtUpdt.executeUpdate("UPDATE bookcopies SET inBorrow='no' WHERE copyID='"+ copyID+ "';");
				if(update>0)
				{
					Date d1 = null,d2 = null;
					String returnDate;
					rs = stmt.executeQuery("SELECT * FROM borrows WHERE borrowStatus='active' AND copyID='" + copyID + "';");
					if(rs.next())
					{
						int borrowID=rs.getInt("borrowID");
						int update2;
						returnDate=rs.getString("returnDate");
						DateFormat retDay=new SimpleDateFormat("yyyy-MM-dd");
						try {
							d1=(Date) retDay.parse(returnDate);
							d2=(Date) retDay.parse(today);
						} catch (ParseException e) {
							e.printStackTrace();
						}
						
						if(d1.getTime()>=d2.getTime())
						{
							update2= stmtUpdt.executeUpdate("UPDATE borrows SET borrowStatus='inactive', actualReturnDate='"+ today +"' WHERE copyID='"+ copyID+ "';");
							Statement st=sqlCon.conn.createStatement();
							System.out.println("borrow id is:");
							System.out.println(borrowID);
							int update3= st.executeUpdate("UPDATE errors SET errorStatus='inactive' WHERE borrowErrorID='"+ borrowID + "';");
						}
						else
						{
							Statement st=sqlCon.conn.createStatement();
							update2= stmtUpdt.executeUpdate("UPDATE borrows SET hasProblem='yes',problemDescription='late in return',borrowStatus='inactive', actualReturnDate='"+ today +"' WHERE copyID='"+ copyID+ "';");
							int update3= st.executeUpdate("UPDATE errors SET errorStatus='inactive' WHERE borrowErrorID='"+ borrowID + "';");
						}
						if(update2>0)
						{
							rs=stmt.executeQuery("SELECT userOrderID, orderDate FROM orders WHERE orderStatus='active' AND copyOrderID='"+copyID+"' GROUP BY orderDate;");		
							if(rs.next())
							{
									int id=rs.getInt(1);
									Integer userID=rs.getInt("userOrderID");
									ResultSet rs1 = stmt.executeQuery("SELECT email FROM users WHERE userID='"+id+"';");
									if(rs1.next())
									{
										String copy=Integer.toString(copyID);
										String email=rs1.getString("email");
										//here we need to send email for the user that stored on rs.getString("userOrderID")
										try {
											ms.Send("ortbraudelibraryg3", "atuda2010", email, "Order Announcement", "You can now order copy: "+copy);
										} catch (MessagingException e) {
											JOptionPane.showMessageDialog(null, "Couldn't send EMAIL.","ERROR", JOptionPane.ERROR_MESSAGE);
											e.printStackTrace();
										}
										update=stmtUpdt.executeUpdate("UPDATE orders SET orderStatus='inactive',inLibrary='yes' WHERE userOrderID='"+userID+"'AND copyOrderID='"+copyID+"';");
										if(update>0)
										{
											rs = stmt.executeQuery("SELECT * FROM orders WHERE orderStatus='active' AND copyOrderID='"+copy+"';");
											if(rs.next())
												res.add("OK");
											else
											{
												System.out.println("THERE ARE NO ORDERS FOR THIS COPYUD!");
												update = stmtUpdt.executeUpdate("UPDATE bookcopies SET ordered='no' WHERE copyID='"+copy+"';");
												if(update>0)
													res.add("OK");
												else
													res.add("Fail");
											}
										}
										else
											res.add("Fail");
									}
									else
										res.add("Fail");
							}
							else
								res.add("Fail");
						}
						else
							res.add("Fail");
					}
					else
						res.add("Fail");				
				}
				else
					res.add("Fail");
			}
			else
				res.add("Fail");	
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for returning book data by search attribute
		 */
		if(command.equals("SearchBook"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String attribute = (String) messages.get(3);
			
			if(attribute.equals("BookName"))
			{
				String bookName = (String) messages.get(2);
				ResultSet rs = stmt.executeQuery("SELECT * FROM books WHERE name='" + bookName + "';");
				if (rs.next()) {
					res.add(rs);
				}
				else res.add("ERROR");
			}
			
			if(attribute.equals("Subject"))
			{
				String subject = (String) messages.get(2);
				ResultSet rs = stmt.executeQuery("SELECT * FROM subjects WHERE subject='" + subject + "';");
				if (rs.next()) {
					ResultSet rs2 = stmt.executeQuery("SELECT * FROM subjectsofbooks WHERE subjectID='" + rs.getString(0) + "';");
					while(rs2.next()){
						ResultSet rs3 = stmt.executeQuery("SELECT * FROM books WHERE bookCode='" + rs2.getString(0) + "';");
						while(rs3.next()){
							res.add(rs3);
						}
					}
				}
				else res.add("ERROR");
			}
			
			if(attribute.equals("Author"))
			{
				String author =  (String) messages.get(2);
				ResultSet rs = stmt.executeQuery("SELECT * FROM books WHERE authors='" + author + "';");
				if (rs.next()) {
					res.add(rs);
				}
				else res.add("ERROR");
			}
			
			client.sendToClient(res);
		}
		/**
		 * Query controller for manually extending the return book of a specific copy for a specific user
		 */
		if(command.equals("ManualExtension"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int userID=(int)messages.get(2);
			int copyID=(int)messages.get(3);
			int extenderID=(int)messages.get(4);

			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			System.out.println(today);
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE cardStatus='ok' AND userID='" + userID + "';");
			if(rs.next())
			{
				ResultSet rs1=stmt.executeQuery("SELECT * FROM bookcopies WHERE ordered='no' AND copyID='" + copyID + "';");
				int bookCode;
				String rated;
				if(rs1.next())
				{
					bookCode=rs1.getInt("bookCode");
					rs1=stmt.executeQuery("SELECT * FROM borrows WHERE borrowStatus='active' AND copyID='" + copyID + "';");
					if(rs1.next())
					{
						Calendar d3 = Calendar.getInstance();
                        Calendar d4 = Calendar.getInstance();
                        d3.setTime(rs.getDate("borrowDate")) ;
                        d4.setTime(rs.getDate("actualReturnDate"));
                        long days=(d4.getTime().getTime() - d3.getTime().getTime()) / (24 * 3600 * 1000);
						Date d1 = null,d2 = null;
						String returnDate = rs1.getString("returnDate");
						DateFormat retDay=new SimpleDateFormat("yyyy-MM-dd");
						try {
							d1=(Date) retDay.parse(returnDate);
							d2=(Date) retDay.parse(today);
						} catch (ParseException e) {
							e.printStackTrace();
						}
						if(d2.compareTo(d1)<=0)
						{
							Statement stmtUpdt = sqlCon.conn.createStatement();
							rs1=stmt.executeQuery("SELECT * FROM books WHERE bookCode='"+ bookCode+"';");
							rs1.next();
							rated=rs1.getString("Rated");
							if(rated.equals("yes"))
							{
								res.add("Fail");
							}
							else if(rated.equals("no") && days<12)
							{
								cal.add(Calendar.DATE, 3);
								String isRated=sdf.format(cal.getTime());
								int update= stmtUpdt.executeUpdate("UPDATE borrows SET wasManualExtended='yes', extenderID='" + extenderID + "', manualExtensionDate='" + today + "',returnDate='" + isRated+ "',actualReturnDate='"+ isRated +  "' WHERE copyID='"+ copyID + "'AND borrowStatus='active';");
								if(update>0)
									res.add("OK");
								else
									res.add("Fail");
							}	
						}
						else
							res.add("Fail");	
					}
					else
						res.add("Fail");
				}
				else
					res.add("Fail");
				rs1.close();
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for adding a new user and information about him to the database 
		 */
		if(command.equals("AddNewUser"))
		{
			Statement stmt = null;
			
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String name = (String) messages.get(2);
			int id = (int) messages.get(3);
			String email = (String) messages.get(4);
			String phone = (String) messages.get(5);
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			
			String AB = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
			Random rnd = new Random();
			int len=6;
			StringBuilder sb = new StringBuilder( len );
			for( int i = 0; i < len; i++ ) 
			    sb.append( AB.charAt( rnd.nextInt(AB.length()) ) );
			String password=sb.toString();
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE id='" + id + "';");
			if(rs.next())
			{
				res.add("Error");
			}
			else
			{
				int updateResult=stmt.executeUpdate("INSERT INTO users (userID, id, username, phone, email, password, status, role, cardStatus, registrationDate) VALUES('" + id + "','" + id + "','" + name + "','" + phone + "','" + email + "','" + password  + "', '0','1','ok','"+today+"');");
				if(updateResult>0)
				{
					res.add("OK");
					res.add(id);
					res.add(password);
				}
				rs.close();
			}
			client.sendToClient(res);
		}
		/**
		 * Query controller for viewing the information about all the borrows for a specific user
		 */
		if(command.equals("ViewBorrowsForUser"))
		{
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows,books,bookcopies WHERE borrows.copyID=bookcopies.copyID AND bookcopies.bookCode=books.bookCode AND borrows.borrowStatus='active' AND borrows.userID='" + userID + "';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					String[] borrow = new String[4];
					String cid=Integer.toString(rs.getInt("copyID"));			
					Date d1 = null,d2 = null;
					String returnDate = rs.getString("returnDate");
					DateFormat retDay=new SimpleDateFormat("yyyy-MM-dd");
					try {
						d1=(Date) retDay.parse(returnDate);
						d2=(Date) retDay.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}
					borrow[0]=rs.getString("name");
					borrow[1]=cid;
					borrow[2]=returnDate;


					if(d2.compareTo(d1)>0)
					{
						borrow[3]="late";
					}
					else
						borrow[3]="ok";
                    res.add(borrow);
				}
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for adding a new book and the information about him to the database		 
		 */
		if(command.equals("addBook"))
		{
			int bookCode = 0;
			int flag=0, num = 0;
			String bookName=(String) messages.get(2);
			String author=(String) messages.get(3);
			String edition=(String) messages.get(4);
			String strFilePath = "C://Users//Lidor//workspace//"+bookName+edition+".doc";
			String printDate=(String) messages.get(5);
			String summary=(String) messages.get(6);
			String rated=(String) messages.get(7);
			String purchase=(String) messages.get(8);
			String shelf=(String) messages.get(9);
			byte [] bytesArray=(byte[]) messages.get(10);
			int length=(int) messages.get(11);
			String subject=(String) messages.get(12);
			
			FileOutputStream fos=new FileOutputStream(strFilePath);
			fos.write(bytesArray, 0, length);
			
			Statement stmt = null;
			
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			ResultSet rs = stmt.executeQuery("SELECT * FROM books;");
			while(rs.next())
			{
				num= rs.getInt("numOfCopies");
				num++;
				bookCode= rs.getInt("bookCode");
				String name= rs.getString("name");
				String ed= rs.getString("edition");
				String aut= rs.getString("authors");
				if(name.equals(bookName) && ed.equals(edition) && aut.equals(author))
					flag=1;
			}
			if(flag==1)
			{
				int result=stmt.executeUpdate("INSERT INTO bookcopies (purchaseDate,bookCode) VALUES ('"+purchase+"','"+bookCode+"');");
				if(result>0)
				{
					result=stmt.executeUpdate("UPDATE books SET numOfCopies='"+num+"' WHERE bookCode='"+bookCode+"';");
					if(result>0)
						res.add("OK");
				}
				res.add("Fail");
			}
			if(flag==0)
			{
				num=1;
				int result=stmt.executeUpdate("INSERT INTO books (name,authors,edition,printDate,shelf,summary,numOfCopies,tableOfContents,rated) VALUES ('"+bookName+"','"+author+"','"+edition+"','"+printDate+"','"+shelf+"','"+summary+"','1','"+strFilePath+"','"+rated+"');");
				if(result>0)
				{
					rs=stmt.executeQuery("SELECT * FROM books WHERE name='"+bookName+"' AND edition='"+edition+"';");
					if(rs.next())
					{
						bookCode=rs.getInt("bookCode");
						ResultSet rs1=stmt.executeQuery("SELECT * FROM subject WHERE subject='"+subject+"';");
						if(rs1.next())
						{
							int subID=rs1.getInt("subID");
							result=stmt.executeUpdate("INSERT INTO subjectsofbooks(bookOfSubjectCode,subjectID) VALUES('"+bookCode+"','"+subID+"');");
							if(result>0)
							{
								result=stmt.executeUpdate("UPDATE books SET numOfCopies='"+num+"' WHERE bookCode='"+bookCode+"';");
								if(result>0)
								{
									result=stmt.executeUpdate("INSERT INTO bookcopies (purchaseDate,bookCode) VALUES ('"+purchase+"','"+bookCode+"');");
									if(result>0)
										res.add("OK");
									else
										res.add("Fail");
								}
								else
									res.add("Fail");
							}
							else
								res.add("Fail");
						}
						else
							res.add("Fail");
					}
					else
						res.add("Fail");
				}
				else
					res.add("Fail");
			}
			rs.close();
			client.sendToClient(res);
		}
	}	
}

